在傳統 OLTP 資料庫中，刪除與更新資料是家常便飯，但在 ClickHouse 這類專為 OLAP 場景設計的資料庫中，「邏輯刪除」與「資料版本控制」則需要透過特別設計的儲存引擎來實現。

CollapsingMergeTree 就是 ClickHouse 提供的一種能夠自動處理資料新增與刪除標記（邏輯刪除）的特殊 MergeTree 引擎。

什麼是 CollapsingMergeTree？

CollapsingMergeTree 是 ClickHouse MergeTree 家族的一員，設計用來解決「資料新增與刪除標記」的場景。
其運作邏輯是透過一個稱為 sign 的欄位標記資料的新增或刪除狀態，在背景 Merge 操作時，自動將 sign 相反的紀錄抵消 (collapse) 以實現邏輯刪除效果。

核心特性：

• 透過 sign 欄位標記資料狀態：1 代表新增，-1 代表刪除。
• Merge 階段會自動將相同 Primary Key 下的 1 / -1 抵消掉。
• 資料刪除為「最終一致性」動作（非即時刪除）。

語法

建立

CREATE TABLE user_actions
(
    user_id UInt64,
    action String,
    sign Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY user_id;

插入

-- 新增行為紀錄
INSERT INTO user_actions VALUES (1, 'login', 1);
INSERT INTO user_actions VALUES (2, 'purchase', 1);

-- 邏輯刪除 user_id = 2 的紀錄
INSERT INTO user_actions VALUES (2, 'purchase', -1);

查詢

SELECT * FROM user_actions;

會看到資料仍然存在，但進行 Merge 後（FINAL）：

OPTIMIZE TABLE user_actions FINAL;

user_id = 2 的紀錄將被抵消 (collapse) 掉，只剩下 user_id = 1 的資料。

運作原理

1. 資料寫入時不進行即時去重，所有資料（含 sign = -1）都會被寫入磁碟。
2. 在背景合併 (Merge) 操作時，ClickHouse 會根據 Primary Key 對應的資料進行配對：
   • 1 筆 sign = 1 與 1 筆 sign = -1 的資料將被抵消 (collapse)。
   • 若 sign 數量不平衡 (如 sign = 1 多餘 sign = -1)，則會保留差值的紀錄。
3. 查詢時不會自動隱藏尚未抵消的資料，若要查詢已 collapse 的最終結果，可透過 FINAL 查詢保證一致性。
4. 資料刪除是最終一致性動作，在 Merge 完成前仍會顯示原始資料。

應用場景

與 ReplacingMergeTree 的差異比較

查詢時的 FINAL 使用與效能注意

因為 CollapsingMergeTree 的去重與刪除動作發生於 Merge 階段，在查詢時若需要立即看到最終一致的結果，必須加上 FINAL 關鍵字：

SELECT * FROM user_actions FINAL;

FINAL 的效能注意：

• FINAL 查詢會強制進行去重計算，若資料量大會顯著增加查詢負擔。
• 不建議頻繁全表 FINAL 查詢，可透過定期執行 OPTIMIZE TABLE ... FINAL 來維護資料一致性。

設計範例

Event Streaming 去重場景：

假設從 Kafka 接收用戶行為資料，可能會出現重複寫入的情況，設計方式如下：

CREATE TABLE user_events
(
    event_time DateTime,
    user_id UInt64,
    event_type String,
    sign Int8
) ENGINE = CollapsingMergeTree(sign)
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time);

• 資料寫入時，重複事件會有一筆 sign = -1 紀錄用於去重。
• 定期執行 OPTIMIZE 確保資料正確性。
• 查詢時如無法等到合併完成，可針對小範圍使用 FINAL 查詢。

Best Practice